using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace WebApplication.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ChartDataController : ControllerBase
    {
        private readonly ILogger<ChartDataController> _logger;

        public ChartDataController(ILogger<ChartDataController> logger)
        {
            _logger = logger;
        }

        [HttpGet("monthly")]
        public IActionResult GetMonthlyData()
        {
            try
            {
                var currentMonth = DateTime.Now.Month;
                var currentYear = DateTime.Now.Year;
                
                // 使用与MLModel相同的数据库连接字符串
                var connectionString = MLModel.RetrainConnectionString;
                var data = new List<object>();

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    
                    // 查询本月的数据
                    var commandText = $"SELECT CAST([Year] as NVARCHAR(MAX)) as Date, CAST([Num] as REAL) as Value FROM [dbo].[Table_] WHERE MONTH(CONVERT(datetime, [Year], 120)) = {currentMonth} AND YEAR(CONVERT(datetime, [Year], 120)) = {currentYear} ORDER BY CONVERT(datetime, [Year], 120)";
                    
                    using (var command = new SqlCommand(commandText, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var date = reader["Date"].ToString();
                                var value = Convert.ToSingle(reader["Value"]);
                                
                                data.Add(new { date, value });
                            }
                        }
                    }
                }

                return Ok(data);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取月度数据时出错");
                return StatusCode(500, "获取数据时发生错误");
            }
        }
        
        [HttpGet("weekly")]
        public IActionResult GetWeeklyData()
        {
            try
            {
                // 计算一周前的日期
                var endDate = DateTime.Now;
                var startDate = endDate.AddDays(-7);
                
                // 使用与MLModel相同的数据库连接字符串
                var connectionString = MLModel.RetrainConnectionString;
                var data = new List<object>();

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    
                    // 查询最近一周的数据
                    var commandText = $"SELECT CAST([Year] as NVARCHAR(MAX)) as Date, CAST([Num] as REAL) as Value FROM [dbo].[Table_] WHERE CONVERT(datetime, [Year], 120) >= '{startDate:yyyy-MM-dd}' AND CONVERT(datetime, [Year], 120) <= '{endDate:yyyy-MM-dd}' ORDER BY CONVERT(datetime, [Year], 120)";
                    
                    using (var command = new SqlCommand(commandText, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var date = reader["Date"].ToString();
                                var value = Convert.ToSingle(reader["Value"]);
                                
                                data.Add(new { date, value });
                            }
                        }
                    }
                }

                return Ok(data);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取周数据时出错");
                return StatusCode(500, "获取数据时发生错误");
            }
        }
    }
}